package com.consume.bhy.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.consume.bhy.jdbc.JDBC_Util;

public class PhotoService {
	
	
	public JSONArray getJsonData() throws SQLException {

		//创建数组存放legend中date数据
		JSONArray legendData = new JSONArray();
		//创建数组存放series的数据
		JSONArray series = new JSONArray();
		//连接操作数据库
		Connection conn = JDBC_Util.getConnection();
		String sql = "select consume_type_id,consume_type_name from consume_type";
		PreparedStatement prepared = conn.prepareStatement(sql);
		ResultSet resultSet = prepared.executeQuery();

		while(resultSet.next()) {
			String sql2 = "select month(consume_date) m, SUM(consume_price*consume_count) s from consume_info where consume_type_id=? group by month(consume_date) ORDER BY month(consume_date);";
			PreparedStatement prepared2 = conn.prepareStatement(sql2);
			prepared2.setInt(1, resultSet.getInt("consume_type_id"));
			ResultSet resultSet2 = prepared2.executeQuery();
		
			double[] consumedate =new double[12];
			while(resultSet2.next()) {
				consumedate[resultSet2.getInt("m")-1]= resultSet2.getBigDecimal("s").doubleValue();
			}
			JDBC_Util.clossJDBC(prepared2, resultSet2);
			JSONObject serie = new JSONObject();

			//将每种消费类型相应的参数存入serie对象并按顺序存入series数组
			serie.put("name", resultSet.getString("consume_type_name"));
		    serie.put("type", "line");
		    serie.put("stack", "总量");
		    serie.put("areaStyle", new JSONObject());
		    serie.put("data", consumedate);
			
		    series.add(serie);
			//将消费类型顺序存入legendData
			legendData.add(resultSet.getString("consume_type_name"));
	
		}
		//最后添加月总消费到legendData
		legendData.add("月总消费");
		
		
		JDBC_Util.clossJDBC(prepared, resultSet);
		
		String sql3 = "select month(consume_date) m, SUM(consume_price*consume_count) s from consume_info group by month(consume_date) ORDER BY month(consume_date);";
		PreparedStatement prepared3 = conn.prepareStatement(sql3);
		ResultSet resultSet3 = prepared3.executeQuery();
	
		double[] allConsume =new double[12];
		while(resultSet3.next()) {
			allConsume[resultSet3.getInt("m")-1]= resultSet3.getBigDecimal("s").doubleValue();
		}
		JDBC_Util.clossJDBC(conn, prepared3, resultSet3);
		
		JSONObject allSerie = new JSONObject();
		
		allSerie.put("name", "月总消费");
		allSerie.put("type", "line");
		allSerie.put("stack", "总量");
		allSerie.put("areaStyle", new JSONObject());
		allSerie.put("data", allConsume);
		//将月总消费的相应的参数存入allSerie对象并存入series数组
	    series.add(allSerie);

		//创建jsonData数组保存legendData和series数组并返回
		JSONArray jsonData = new JSONArray();
		jsonData.add(legendData);
		jsonData.add(series);

		return jsonData;
	}



	

}
